Click Here!
home account info subscribe login search My ITKnowledge FAQ/help site map contact us


 
Brief Full
 Advanced
      Search
 Search Tips
To access the contents, click the chapter and section titles.

Oracle Performance Tuning and Optimization
(Publisher: Macmillan Computer Publishing)
Author(s): Edward Whalen
ISBN: 067230886x
Publication Date: 04/01/96

Bookmark It

Search this book:
 
Previous Table of Contents Next


Chapter 6
Performance Monitoring Tools

Before you configure a system for optimal performance, it is essential that you properly analyze the system. Without having good data about the performance and internal workings of the system, it is difficult to know what to modify. This is true for the hardware, the operating system, and the RDBMS.

This chapter gives you a brief introduction to some of the performance monitoring tools available on the market today. These tools may or may not be useful to you; this short overview will help you make that decision.

Performance monitoring tools fall into several categories: Some tools perform an analysis; other tools display system data; yet others are designed to trap certain error conditions. Each has a different focus and each is valuable to the system administrator. In addition, many good application development tools are available today; Chapter 35, “Using GUI Builders,” details some of these tools, including Oracle Developer/2000, PowerBuilder, Delphi, and others.

Many tools are available to help you design, administer, and monitor your system. You can pick up any database magazine and read about many excellent products. Rather than go into a long discussion on these products, this chapter points out what to look for in some of these tools. It also looks at what is already available in your system to monitor performance and what you can obtain from Oracle.

Oracle stores a wealth of information about how the system is performing in the dynamic performance tables. Many tables make up the dynamic performance tables (refer to your Oracle administrator’s guide). Never access these tables directly; always access them through the views defined in CATALOG.SQL. Table 6.1 describes a few of the views that are of significance to performance tuning.

Table 6.1 Dynamic Performance Tables Valuable in Performance Tuning

View Description

V$ACCESS Information about locked objects in the database and who is accessing them.
V$CIRCUIT Information about virtual circuits. A virtual circuit is a connection into the database through dispatchers and servers.
V$DB_OBJECT_CACHE Database objects (tables, indexes, clusters, synonym definitions, PL/SQL procedures and packages, and triggers) cached in the library cache.
V$DISPATCHER Dispatcher process information.
V$FILESTAT Information on file read/write statistics. This information can be enhanced by setting the parameter TIMED_STATISTICS equal to TRUE. Because setting this parameter degrades performance, use it only if you feel it is necessary to debug a problem.
V$LATCH Information about each type of latch; used to indicate latch contention.
V$LIBRARYCACHE Statistics about library cache management. Look here for information about the number of library cache hits.
V$LOCK Information about locks and resources; does not include information about DDL locks.
V$QUEUE Information about the multithreaded message queues. Look here for information necessary to determine average wait time per item.
V$REQDIST Histogram of request time, divided into 12 buckets; can provide valuable information about request times.
V$ROLLSTAT Statistics for all online rollback segments. Look here for information about whether rollback segments are properly configured.
V$ROWCACHE Statistics for all data dictionary activity.
V$SESSION_WAIT List of resources or events for which active sessions are waiting; can be useful in debugging a particular problem.
V$SESSTAT The current statistic values for each current session; used with V$STATNAME.
V$SESS_IO I/O statistics for each user session.
V$SYSSTAT The current system-wide value for each statistic in V$SESSTAT; also used with V$STATNAME.
V$WAITSTAT Block contention statistics. This information is stored only when the parameter TIMED_STATISTICS is set to TRUE. Setting this parameter causes extra overhead and degrades performance; use it only if you feel it is necessary to debug a problem.

The dynamic performance tables include many more tables than are mentioned here. Most tables are used internally and are not of use in performance tuning unless you are debugging a specific problem.

Oracle Tools

Oracle offers several performance monitoring tools. You automatically get SQL*DBA as part of the Oracle RDBMS product, which includes a monitor interface into the V$ tables. You can also get Server Manager from Oracle (which eventually will replace SQL*DBA as the standard administrative tool). Server Manager (introduced in Oracle version 7.1) is similar to SQL*DBA but provides an optional Graphical User Interface version.

Oracle recently introduced SNMP (Simple Network Management Protocol) agents. These agents (as well as any of the third-party vendors who have been working with Oracle) provide an interface into the V$ views by using an SNMP management console. Also new from Oracle is a graphical version of SQL Trace. The SQL Trace tool is designed to help track down and fix inefficient SQL statements in your application. Of course, the EXPLAIN PLAN command is always useful in analyzing SQL code.

SQL*DBA Monitor

You can use the SQL*DBA monitor command to display real-time information about locks, enqueues, processes, file I/O, and other system statistics. Monitor is used as a shortcut to display information stored in the V$ tables. Monitor has been around as long as SQL*DBA; with the emergence of Server Manager, Monitor may not be of use much longer.

Server Manager

Server Manager is now available on several platforms. Server Manager is similar to SQL*DBA in that it is designed to assist in the administration of the Oracle database. But where SQL*DBA is character based, Server Manager provides a Graphical User Interface. From Server Manager, you can display much of the information available in the V$ tables.


Previous Table of Contents Next


Products |  Contact Us |  About Us |  Privacy  |  Ad Info  |  Home

Use of this site is subject to certain Terms & Conditions, Copyright © 1996-2000 EarthWeb Inc.
All rights reserved. Reproduction whole or in part in any form or medium without express written permission of EarthWeb is prohibited.